package br.com.cinesystem.persistence;

import br.com.cinesystem.entity.Cinema;
import br.com.cinesystem.entity.Filme;
import br.com.cinesystem.entity.Sala;
import br.com.cinesystem.entity.Sessao;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Locale;


public class SessaoDao extends Dao {

    public SessaoDao() {
    }

    public void salvar(Sessao s) throws Exception{
        abrirConexao();

        SimpleDateFormat dformata = new SimpleDateFormat("yyyy/MM/dd");
        String data = dformata.format(s.getData());

        SimpleDateFormat hformatada = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        String hora = hformatada.format(s.getHora());

        stmt = con.prepareStatement("insert into sessao values(null,?,?,?,?,?,?,1)");
        stmt.setInt(1, s.getSala().getIdSala());
        stmt.setInt(2, s.getFilme().getIdFilme());
        stmt.setString(3, data);
        stmt.setString(4, hora);
        stmt.setDouble(5, s.getPreco());
        stmt.setString(6, s.getDetalhes());


        stmt.execute();

        fecharConexao();
    }

    public List<Sessao> buscarTodos()throws Exception{

        abrirConexao();

        Date data = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String d = sdf.format(data);

        StringBuilder sb = new StringBuilder();
        sb.append("SELECT f.idFilme, f.titulo, c.idCinema, c.razaoSocial, se.preco, ");
        sb.append("se.idSessao, se.data, se.hora, se.detalhes, sa.idSala, sa.nome, sa.capacidade ");
        sb.append("FROM filme f INNER JOIN sessao se ");
        sb.append("ON se.id_Filme = f.idFilme ");
        sb.append("INNER JOIN sala sa ");
        sb.append("ON se.id_Sala = sa.idSala ");
        sb.append("INNER JOIN cinema c ");
        sb.append("ON sa.id_Cinema = c.idCinema ");
        sb.append("WHERE f.situacao=1 and sa.situacao=1 and c.situacao=1 and se.situacao=1 and data>=?");
        sb.append("ORDER BY data, hora");

        stmt = con.prepareStatement(sb.toString());
        stmt.setString(1, d);

        rs = stmt.executeQuery();

        List<Sessao> lista = new ArrayList<Sessao>();

        while (rs.next()){

            Sessao sessao = new Sessao();
            sessao.setFilme(new Filme());
            sessao.setSala(new Sala());
            sessao.getSala().setCinema(new Cinema());

            sessao.getFilme().setIdFilme(rs.getInt("idFilme"));
            sessao.getFilme().setTitulo(rs.getString("titulo"));
            sessao.getSala().setIdSala(rs.getInt("idSala"));
            sessao.getSala().setNome(rs.getString("nome"));
            sessao.getSala().setCapacidade(rs.getInt("capacidade"));
            sessao.getSala().getCinema().setIdCinema(rs.getInt("idCinema"));
            sessao.getSala().getCinema().setRazaoSocial(rs.getString("razaoSocial"));

            SimpleDateFormat sdf1 = new SimpleDateFormat("dd/MM/yyyy");
            SimpleDateFormat sdf2 = new SimpleDateFormat("HH:mm:ss");

            sessao.setIdSessao(rs.getInt("idSessao"));
            sessao.setData((Date) sdf1.parse(sdf1.format(rs.getDate("data"))));
            sessao.setHora((Date) sdf2.parse(sdf2.format(rs.getTime("hora"))));
            sessao.setDetalhes(rs.getString("detalhes"));
            sessao.setPreco(rs.getDouble("preco"));
            
            lista.add(sessao);
        }

        fecharConexao();
        return lista;
    }

    public Sessao buscar(Sessao s)throws Exception{

        abrirConexao();

        StringBuilder sb = new StringBuilder();
        sb.append("SELECT f.idFilme, f.titulo, c.idCinema, c.razaoSocial, se.preco, ");
        sb.append("se.idSessao, se.detalhes, se.data, se.hora, sa.idSala, sa.nome, sa.capacidade ");
        sb.append("FROM filme f INNER JOIN sessao se ");
        sb.append("ON se.id_Filme = f.idFilme ");
        sb.append("INNER JOIN sala sa ");
        sb.append("ON se.id_Sala = sa.idSala ");
        sb.append("INNER JOIN cinema c ");
        sb.append("ON sa.id_Cinema = c.idCinema ");
        sb.append("WHERE se.idSessao=?");

        stmt = con.prepareStatement(sb.toString());
        stmt.setInt(1, s.getIdSessao());

        rs = stmt.executeQuery();

        Sessao sessao = null;

        if(rs.next()){

            sessao = new Sessao();
            sessao.setFilme(new Filme());
            sessao.setSala(new Sala());
            sessao.getSala().setCinema(new Cinema());

            sessao.getFilme().setIdFilme(rs.getInt("idFilme"));
            sessao.getFilme().setTitulo(rs.getString("titulo"));
            sessao.getSala().setIdSala(rs.getInt("idSala"));
            sessao.getSala().setNome(rs.getString("nome"));
            sessao.getSala().setCapacidade(rs.getInt("capacidade"));
            sessao.getSala().getCinema().setIdCinema(rs.getInt("idCinema"));
            sessao.getSala().getCinema().setRazaoSocial(rs.getString("razaoSocial"));

            SimpleDateFormat sdf1 = new SimpleDateFormat("dd/MM/yyyy");
            SimpleDateFormat sdf2 = new SimpleDateFormat("HH:mm:ss");

            sessao.setData((Date) sdf1.parse(sdf1.format(rs.getDate("data"))));
            sessao.setHora((Date) sdf2.parse(sdf2.format(rs.getTime("hora"))));
            sessao.setIdSessao(rs.getInt("idSessao"));
            sessao.setPreco(rs.getDouble("preco"));
            sessao.setDetalhes(rs.getString("detalhes"));
        }

        fecharConexao();
        return sessao;
    }

    public boolean verificarHoraInicio(Calendar d1, Calendar d2, Sessao s)throws Exception{
        abrirConexao();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String data1 = sdf.format(d1.getTime());
        String data2 = sdf.format(d2.getTime());

        stmt = con.prepareStatement("SELECT * FROM sessao WHERE (hora BETWEEN ? AND ?) and id_Sala=?");
        stmt.setString(1, data1);
        stmt.setString(2, data2);
        stmt.setInt(3, s.getSala().getIdSala());
        
        rs = stmt.executeQuery();

        boolean var = rs.next();
        fecharConexao();
        return var;
    }

   

      public List<Sessao> buscarPorData(Sessao s)throws Exception{

        abrirConexao();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String d = sdf.format(s.getData());

        StringBuilder sb = new StringBuilder();
        sb.append("SELECT f.idFilme, f.titulo, f.duracao, c.idCinema, c.razaoSocial, se.preco, ");
        sb.append("se.idSessao, se.detalhes, se.data, se.hora, sa.idSala, sa.nome, sa.capacidade ");
        sb.append("FROM filme f INNER JOIN sessao se ");
        sb.append("ON se.id_Filme = f.idFilme ");
        sb.append("INNER JOIN sala sa ");
        sb.append("ON se.id_Sala = sa.idSala ");
        sb.append("INNER JOIN cinema c ");
        sb.append("ON sa.id_Cinema = c.idCinema ");
        sb.append("WHERE se.data=? and se.id_Sala=?");

        stmt = con.prepareStatement(sb.toString());
        stmt.setString(1, d);
        stmt.setInt(2, s.getSala().getIdSala());

        rs = stmt.executeQuery();

        List<Sessao> lista = new ArrayList<Sessao>();

        while (rs.next()){

            Sessao sessao = new Sessao();
            sessao.setFilme(new Filme());
            sessao.setSala(new Sala());
            sessao.getSala().setCinema(new Cinema());

            sessao.getFilme().setIdFilme(rs.getInt("idFilme"));
            sessao.getFilme().setTitulo(rs.getString("titulo"));
            sessao.getFilme().setDuracao(rs.getInt("duracao"));
            sessao.getSala().setIdSala(rs.getInt("idSala"));
            sessao.getSala().setNome(rs.getString("nome"));
            sessao.getSala().setCapacidade(rs.getInt("capacidade"));
            sessao.getSala().getCinema().setIdCinema(rs.getInt("idCinema"));
            sessao.getSala().getCinema().setRazaoSocial(rs.getString("razaoSocial"));

            SimpleDateFormat sdf1 = new SimpleDateFormat("dd/MM/yyyy");
            SimpleDateFormat sdf2 = new SimpleDateFormat("ddd/MM/yyyy HH:mm:ss");

            sessao.setIdSessao(rs.getInt("idSessao"));
            sessao.setData((Date) sdf1.parse(sdf1.format(rs.getDate("data"))));
            sessao.setHora((Date) sdf2.parse(sdf2.format(rs.getTimestamp("hora"))));
            sessao.setDetalhes(rs.getString("detalhes"));
            sessao.setPreco(rs.getDouble("preco"));

            lista.add(sessao);
        }

        fecharConexao();
        return lista;
    }

    public void alterar(Sessao s)throws Exception{

        abrirConexao();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String d = sdf.format(s.getData());

        SimpleDateFormat hformatada = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        String hora = hformatada.format(s.getHora());

        stmt = con.prepareStatement("UPDATE sessao SET id_Sala=?, id_Filme=?, data=?, hora=?, preco=?, detalhes=? WHERE idSessao=?");

        stmt.setInt(1, s.getSala().getIdSala());
        stmt.setInt(2, s.getFilme().getIdFilme());
        stmt.setString(3, d);
        stmt.setString(4, hora);
        stmt.setDouble(5, s.getPreco());
        stmt.setString(6, s.getDetalhes());
        stmt.setInt(7, s.getIdSessao());

        stmt.execute();

        fecharConexao();
    }

    public void excluir(Sessao s) throws Exception{

        abrirConexao();

        stmt = con.prepareStatement("UPDATE sessao SET situacao=0 WHERE idSessao=?");

        stmt.setInt(1, s.getIdSessao());

        stmt.execute();

        fecharConexao();

    }

    public List<Sessao> buscarCinemasFilmes(Filme f)throws Exception{

        abrirConexao();

        StringBuilder sb = new StringBuilder();

        Date data = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String d = sdf.format(data);

        sb.append("SELECT f.idFilme, f.titulo, c.idCinema, c.razaoSocial, se.preco, ");
        sb.append("se.idSessao, se.data, se.hora, se.detalhes, sa.idSala, sa.nome, sa.capacidade ");
        sb.append("FROM filme f INNER JOIN sessao se ");
        sb.append("ON se.id_Filme = f.idFilme ");
        sb.append("INNER JOIN sala sa ");
        sb.append("ON se.id_Sala = sa.idSala ");
        sb.append("INNER JOIN cinema c ");
        sb.append("ON sa.id_Cinema = c.idCinema ");
        sb.append("WHERE f.situacao=1 and sa.situacao=1 and c.situacao=1 and se.situacao=1 ");
        sb.append("and idFilme=? and idCinema=? and data>=?");
        sb.append("ORDER BY idSala, data, hora");

        stmt = con.prepareStatement(sb.toString());
        stmt.setInt(1, f.getIdFilme());
        stmt.setInt(2, f.getCinemaList().get(0).getIdCinema());
        stmt.setString(3, d);

        rs = stmt.executeQuery();

        List<Sessao> lista = new ArrayList<Sessao>();


        while(rs.next()){

            Sessao sessao = new Sessao();
            sessao.setFilme(new Filme());
            sessao.setSala(new Sala());
            sessao.getSala().setCinema(new Cinema());

            sessao.getFilme().setIdFilme(rs.getInt("idFilme"));
            sessao.getFilme().setTitulo(rs.getString("titulo"));
            sessao.getSala().setIdSala(rs.getInt("idSala"));
            sessao.getSala().setNome(rs.getString("nome"));
            sessao.getSala().setCapacidade(rs.getInt("capacidade"));
            sessao.getSala().getCinema().setIdCinema(rs.getInt("idCinema"));
            sessao.getSala().getCinema().setRazaoSocial(rs.getString("razaoSocial"));

            SimpleDateFormat sdf1 = new SimpleDateFormat("dd/MM/yyyy");
            SimpleDateFormat sdf2 = new SimpleDateFormat("HH:mm:ss");

            sessao.setIdSessao(rs.getInt("idSessao"));
            sessao.setData((Date) sdf1.parse(sdf1.format(rs.getDate("data"))));
            sessao.setHora((Date) sdf2.parse(sdf2.format(rs.getTime("hora"))));
            sessao.setDetalhes(rs.getString("detalhes"));
            sessao.setPreco(rs.getDouble("preco"));

            lista.add(sessao);
        }

        fecharConexao();
        return lista;
    }

}
